/**
 * 
 */
package com.cipe.cmrs.service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.convert.ConversionService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cipe.cmrs.bean.PaymentRequestBean;
import com.cipe.cmrs.bean.PaymentRequestLineItemBean;
import com.cipe.cmrs.dao.BaseDao;
import com.cipe.cmrs.exception.QueryException;
import com.cipe.cmrs.helper.PaymentHelper;
import com.cipe.cmrs.model.AccountCode;
import com.cipe.cmrs.model.Cipeproject;
import com.cipe.cmrs.model.Paymentrequest;
import com.cipe.cmrs.model.Paymentrequestlineitem;
import com.cipe.cmrs.model.Paymenttype;
import com.cipe.cmrs.model.User;
import com.cipe.cmrs.util.DateUtil;
import com.cipe.cmrs.util.DateUtil2;
import com.cipe.cmrs.util.NumberUtil;
import com.cipe.cmrs.util.UserUtil;
import com.cipe.cmrs.util.Util;

/**
 * @author Incepio
 * 
 */
@Service
@Transactional
public class PaymentrequestService extends BaseService<Paymentrequest> {
    @Autowired
    private ConversionService conversionService;
    @Autowired
    private PaymentrequestlineitemService paymentrequestlineitemService;
    @Autowired
    private PaymentHelper paymentService;
    @Autowired
    private BaseDao<Paymentrequestlineitem> lineItemDao;
    
    /**
     * This method call the databse procedure and return the list
     * 
     * @param procedureName
     * @return
     */
    public List<Paymentrequest> findPaymentrequest(String procedureName) {
        return this.dao.getResultListWithNativeQuery(procedureName, Paymentrequest.class);
    }

    public PaymentRequestBean rejectPaymentRequest(PaymentRequestBean bean, Integer userId) {
        bean.setStatusCode(getText("status.payment.process.rejected"));
//        bean.setDateRejected(new Date());
        User user = UserUtil.INSTANCE.getCurrentUser();
        bean.setUser(user);
        bean.setCurrentlyresponsible(user.getFullName());
        merge(conversionService.convert(bean, Paymentrequest.class));
        return bean;
    }

    public PaymentRequestBean cancelPaymentRequest(PaymentRequestBean bean, Integer userId, String personResponsible) {
   	
        bean.setStatusCode(getText("status.payment.process.cancelled"));
//        bean.setDateCancelled(new Date());
        bean.setCurrentlyresponsible(personResponsible);
        User user = UserUtil.INSTANCE.getCurrentUser();
        bean.setUser(user);
        merge(conversionService.convert(bean, Paymentrequest.class));
        return bean;
    }

    public PaymentRequestBean forwardToFinance(PaymentRequestBean bean, Integer userId) {
        bean.setStatusCode(getText("status.payment.process.finance"));
        bean.setCurrentlyresponsible(getText("status.responsible.payment.process.financeassistant"));
//        bean.setDateToFinanace(new Date());
        merge(conversionService.convert(bean, Paymentrequest.class));
        return bean;
    }

    public PaymentRequestBean processPaymentRequest(PaymentRequestBean bean, Integer userId) {
        if (getText("status.payment.process.new").equals(bean.getStatusCode())
            || getText("status.payment.process.resubmitted").equals(bean.getStatusCode())) {
            bean.setStatusCode(getText("status.payment.process.finance"));
            bean.setCurrentlyresponsible(getText("status.responsible.payment.process.financeassistant"));
           // bean.setDateToFinanace(new Date());
        } else if (getText("status.payment.process.finance").equals(bean.getStatusCode())) {
            bean.setStatusCode(getText("status.payment.process.signature"));
           // bean.setDateforwardedforsignature(new Date());
            bean.setCurrentlyresponsible(getText("status.responsible.payment.process.signatories"));
        } else if (getText("status.payment.process.signature").equals(bean.getStatusCode())) {
            bean.setStatusCode(getText("status.payment.process.pendingpayment"));
         //   bean.setDatesubmittedforpayment(new Date());
            if ("check".equalsIgnoreCase(bean.getPaymentMethod())) {
                bean.setCurrentlyresponsible(getText("status.responsible.payment.process.outsideaccountant"));
            } else if ("wire".equalsIgnoreCase(bean.getPaymentMethod())) {
                if (bean.getAmount() == null || new BigDecimal("50000").compareTo(bean.getAmount()) > 0) {
                    bean.setCurrentlyresponsible(getText("status.responsible.payment.process.financemanager"));
                } else {
                    bean.setCurrentlyresponsible(getText("status.responsible.payment.process.deputydirector"));
                }
            }
        } else if (getText("status.payment.process.pendingpayment").equals(bean.getStatusCode())) {
            bean.setStatusCode(getText("status.payment.process.paid"));
            bean.setCurrentlyresponsible(getText("status.responsible.payment.process.financeassistant"));
         //   bean.setDatePaid(new Date());
        } else if (getText("status.payment.process.cancelrequested").equals(bean.getStatusCode())) {
            // paymentRequest.setCurrentlyresponsible(getText("status.responsible.payment.process.financeassistant"));
        }
        User user = UserUtil.INSTANCE.getCurrentUser();
        bean.setUser(user);
        merge(conversionService.convert(bean, Paymentrequest.class));
        return bean;
    }

    public PaymentRequestBean setCancelRequested(PaymentRequestBean bean, Integer userId) {
    //    bean.setDateCancelRequested(new Date());
        bean.setStatusCode(getText("status.payment.process.cancelrequested"));
        merge(conversionService.convert(bean, Paymentrequest.class));
        return bean;
    }

    public PaymentRequestBean resubmitPayment(PaymentRequestBean bean, Integer userId) {
        bean.setStatusCode(getText("status.payment.process.resubmitted"));
        bean.setCurrentlyresponsible(getText("status.responsible.payment.process.financeassistant"));
       // bean.setDateResubmitted(new Date());
        merge(conversionService.convert(bean, Paymentrequest.class));
        return bean;
    }

    public Paymentrequest savePaymentrequest(Paymentrequest paymentrequest, Integer userId) {
        if (paymentrequest != null) {
            User user = UserUtil.INSTANCE.getCurrentUser();
            Paymentrequest dbPaymentrequest = findById(paymentrequest.getId());
            if (dbPaymentrequest == null) {
                dbPaymentrequest = new Paymentrequest();
                dbPaymentrequest.setCreatedBy(user);
                dbPaymentrequest.setCreationDate(DateUtil.getCurrentDate());
            } else {
                dbPaymentrequest.setUpdatedBy(user);
                dbPaymentrequest.setUpdationDate(DateUtil.getCurrentDate());
            }
            // setter
            Paymenttype paymentType = paymentrequest.getPaymenttype();
            if (paymentType.getId() != null && paymentType.getId() > 0) {
                dbPaymentrequest.setPaymenttype(paymentType);
            } else {
                dbPaymentrequest.setPaymenttype(null);
            }
            dbPaymentrequest.setPaymentIdentifiedInformation(paymentrequest.getPaymentIdentifiedInformation());
            dbPaymentrequest.setReceivedFromAccountingDate(paymentrequest.getReceivedFromAccountingDate());
            // dbPaymentrequest.setPaymentrequesttype(paymentrequest.getPaymentrequesttype());
            dbPaymentrequest.setPaymentmethod(paymentrequest.getPaymentmethod());
            dbPaymentrequest.setPayee(paymentrequest.getPayee());
            dbPaymentrequest.setPurposeOfExpenditure(paymentrequest.getPurposeOfExpenditure());
            dbPaymentrequest.setSpecialInstructions(paymentrequest.getSpecialInstructions());
            dbPaymentrequest.setInvoiceRequestDate(paymentrequest.getInvoiceRequestDate());
            dbPaymentrequest.setInvoicenumber(paymentrequest.getInvoicenumber());
            dbPaymentrequest.setDateToFinanace(paymentrequest.getDateToFinanace());
            dbPaymentrequest.setStatusCode(paymentrequest.getStatusCode());
            dbPaymentrequest.setAmount(paymentrequest.getAmount());
            dbPaymentrequest.setCurrentlyresponsible(paymentrequest.getCurrentlyresponsible());

            savePaymentLineItems(dbPaymentrequest, paymentrequest, user);

            dbPaymentrequest = merge(dbPaymentrequest);

            // Fix for 0131-95
            dbPaymentrequest.getLineItemBeans().clear();
            for (Paymentrequestlineitem item : dbPaymentrequest.getPaymentrequestlineitems()) {
                item.setLineitemnumber(item.getId());
                dbPaymentrequest.getLineItemBeans().add(conversionService.convert(item, PaymentRequestLineItemBean.class));
            }
            return dbPaymentrequest;
        }
        return null;
    }

    public PaymentRequestBean savePaymentrequest(PaymentRequestBean bean, Integer userId) {
        User user = UserUtil.INSTANCE.getCurrentUser();
        bean.setUser(user);
        Paymentrequest request = merge(conversionService.convert(bean, Paymentrequest.class));
        return conversionService.convert(request, PaymentRequestBean.class);
    }

    public PaymentRequestBean getPaymentRequestbean(Integer id) {
        return conversionService.convert(findById(id), PaymentRequestBean.class);
    }

    private void savePaymentLineItems(Paymentrequest dbPaymentrequest, Paymentrequest paymentRequestBean, User user) {
        Map<Integer, Paymentrequestlineitem> map = new HashMap<Integer, Paymentrequestlineitem>();

        List<Paymentrequestlineitem> list = dbPaymentrequest.getPaymentrequestlineitems();
        if (list == null) {
            list = new ArrayList<Paymentrequestlineitem>();
            dbPaymentrequest.setPaymentrequestlineitems(list);
        } else {
            for (Paymentrequestlineitem item : list) {
                map.put(item.getId(), item);
            }
        }

        for (PaymentRequestLineItemBean bean : paymentRequestBean.getLineItemBeans()) {
            if (paymentService.hasData(bean)) {
                Paymentrequestlineitem item = map.get(bean.getId());
                if (item == null) {
                    item = new Paymentrequestlineitem();
                    item.setCreatedBy(user);
                    item.setCreationDate(new Date());
                    list.add(item);
                } else {
                    item.setUpdatedBy(user);
                    item.setUpdationDate(new Date());
                    map.remove(bean.getId());
                }

                item.setPaymentrequest(dbPaymentrequest);

                if (bean.getAccountCodeId() != null) {
                    AccountCode accountCode = new AccountCode();
                    accountCode.setId(bean.getAccountCodeId());
                    item.setAccountCode(accountCode);
                } else {
                    item.setAccountCode(null);
                }
                if (bean.getCipeProjectId() != null) {
                    Cipeproject cipeProject = new Cipeproject();
                    cipeProject.setId(bean.getCipeProjectId());
                    item.setCipeproject(cipeProject);
                } else {
                    item.setCipeproject(null);
                }
                item.setPaymentrequestamount(bean.getAmount());
            }
        }

        for (Paymentrequestlineitem item : map.values()) {
            list.remove(item);
        }

        paymentrequestlineitemService.deleteAll(map.values());
    }

    @SuppressWarnings("rawtypes")
    // Fix for UAT-029 - Claris
    // System should not allow a new record to be added where a previously
    // entered record has the same values for 1) invoice number, 2) invoice
    // date, 3) payee and 4) amount (combination of all)
    public boolean isExisting(PaymentRequestBean bean) {
        if (!bean.getPrevInvoicenumber().equalsIgnoreCase(bean.getInvoicenumber())
            || !DateUtil2.INSTANCE.isSameDay(bean.getPrevInvoiceRequestDate(), bean.getInvoiceRequestDate())
            || !NumberUtil.INSTANCE.isEqual(bean.getPrevPayeeId(), bean.getPayeeId())
            || !NumberUtil.INSTANCE.isEqual(bean.getPrevAmount(), bean.getAmount())) {

            int counter = 1;
            List<Object> parameters = new ArrayList<Object>();
            StringBuilder whereBuilder = new StringBuilder();
            if (StringUtils.isNotBlank(bean.getInvoicenumber())) {
                whereBuilder.append("UPPER(obj.invoicenumber) = ?" + counter++);
				parameters.add(bean.getInvoicenumber().toUpperCase());
            } else {
                whereBuilder.append("(obj.invoicenumber IS NULL OR obj.invoicenumber = ?" + counter++ + ")");
                parameters.add("");
            }

            if (bean.getInvoiceRequestDate() != null) {
                whereBuilder.append(" AND obj.invoiceRequestDate BETWEEN ?" + counter++ + " AND ?" + counter++);
                parameters.add(DateUtil2.INSTANCE.getSodDate(bean.getInvoiceRequestDate()));
                parameters.add(DateUtil2.INSTANCE.getEodDate(bean.getInvoiceRequestDate()));
            } else {
                whereBuilder.append(" AND obj.invoiceRequestDate IS NULL");
            }

            if (bean.getPayeeId() != null && bean.getPayeeId() > 0) {
                whereBuilder.append(" AND obj.payee.id = ?" + counter++);
                parameters.add(bean.getPayeeId());
            } else {
                whereBuilder.append(" AND obj.payee IS NULL");
            }

            if (bean.getAmount() != null) {
                whereBuilder.append(" AND obj.amount = ?" + counter++);
                parameters.add(bean.getAmount());
            } else {
                whereBuilder.append(" AND obj.amount IS NULL");
            }
            
			if (bean.getId() != null && bean.getId() > 0) {
				whereBuilder.append(" AND obj.id != ?" + counter++);
				parameters.add(bean.getId());
			}

            String query = "SELECT COUNT(obj) FROM " + Paymentrequest.class.getName() + " obj WHERE " + whereBuilder.toString();
            List results = this.dao.getResultList(query, parameters.toArray(new Object[0]));
            Long total = results != null ? (Long) results.get(0) : 0L;
            return total != null && total > 0;
        } else {
            return false;
        }
    }

    public Set<Paymentrequest> findPaymentrequest(Paymentrequest pr) {
    
        Set<Paymentrequest> results = null;
        if (StringUtils.isNotBlank(pr.getCostcenternumber())
            || (pr.getAccountcode() != null && NumberUtil.INSTANCE.getIntValue(pr.getAccountcode().getId()) > 0)) {

            StringBuilder query = new StringBuilder("SELECT ");
            int i = 0;
            String[] columnNames = getColumnNames(true);
            for (; i < columnNames.length - 1; i++) {
                query.append("obj." + columnNames[i] + ", ");
            }
            query.append("obj." + columnNames[i]);
            query.append(" FROM Paymentrequestlineitem obj");
            query.append(" LEFT JOIN obj.cipeproject");
            query.append(createJoinString(true));

            List<Object> paramValues = new ArrayList<Object>();
            StringBuilder where = new StringBuilder();
            WhereParamTuple wpt = createWhereTuple(true, pr);
            if (StringUtils.isNotBlank(wpt.whereClause)) {
                where.append(wpt.whereClause);
                paramValues.addAll(wpt.paramValues);
            }

            if (StringUtils.isNotBlank(pr.getCostcenternumber())) {
                if (where.length() > 0) {
                    where.append(" AND ");
                }
                where.append("UPPER(obj.cipeproject.costCenterNumber) LIKE ?");
                paramValues.add("%" + pr.getCostcenternumber().toUpperCase() + "%");
            }

            if (pr.getAccountcode() != null && NumberUtil.INSTANCE.getIntValue(pr.getAccountcode().getId()) > 0) {
                if (where.length() > 0) {
                    where.append(" AND ");
                }
                where.append("obj.accountCode.id = ?");
                paramValues.add(pr.getAccountcode().getId());
            }

            if (StringUtils.isNotBlank(where.toString())) {
                query.append(" WHERE ");
                query.append(where.toString());
            }

            List<Paymentrequestlineitem> lineItems = lineItemDao.getResultList(query.toString(), Paymentrequestlineitem.class,
                columnNames, paramValues.toArray(new Object[0]));
            if (lineItems != null && lineItems.size() > 0) {
                results = new HashSet<Paymentrequest>();
                for (Paymentrequestlineitem lineItem : lineItems) {
                    results.add(lineItem.getPaymentrequest());
                }
            }
        } else {
            StringBuilder query = new StringBuilder("SELECT ");
            int i = 0;
            String[] columnNames = getColumnNames(false);
            for (; i < columnNames.length - 1; i++) {
                query.append("obj." + columnNames[i] + ", ");
            }
            query.append("obj." + columnNames[i]);
            query.append(" FROM Paymentrequest obj");
            query.append(createJoinString(false));

            List<Object> paramValues = new ArrayList<Object>();
            WhereParamTuple wpt = createWhereTuple(false, pr);
            if (StringUtils.isNotBlank(wpt.whereClause)) {
                query.append(" WHERE ");
                query.append(wpt.whereClause);
                paramValues.addAll(wpt.paramValues);      
             
            }
            

            List<Paymentrequest> list = dao.getResultList(query.toString(), Paymentrequest.class, columnNames,
                paramValues.toArray(new Object[0]));
            if (list != null && list.size() > 0) {
                results = new HashSet<Paymentrequest>(list);
            }
        }
        return results;
    }

    private String[] getColumnNames(boolean fromLineItem) {
        List<String> columnNames = new ArrayList<String>();
        if (fromLineItem) {
            columnNames.add("paymentrequest.id");
            columnNames.add("paymentrequest.createdBy.firstName");
            columnNames.add("paymentrequest.createdBy.lastName");
            columnNames.add("paymentrequest.creationDate");
            columnNames.add("paymentrequest.amount");
            columnNames.add("paymentrequest.statusCode");
            columnNames.add("paymentrequest.paymenttype.paymenttypename");
            columnNames.add("paymentrequest.statusCode");
            columnNames.add("paymentrequest.payee.payeeName");
            columnNames.add("paymentrequest.paymentmethod");
            columnNames.add("paymentrequest.invoicenumber");
            columnNames.add("paymentrequest.purposeOfExpenditure");
        } else {
            columnNames.add("id");
            columnNames.add("createdBy.firstName");
            columnNames.add("createdBy.lastName");
            columnNames.add("creationDate");
            columnNames.add("amount");
            columnNames.add("statusCode");
            columnNames.add("paymenttype.paymenttypename");
            columnNames.add("statusCode");
            columnNames.add("payee.payeeName");
            columnNames.add("paymentmethod");
            columnNames.add("invoicenumber");
            columnNames.add("purposeOfExpenditure");
        }
        return columnNames.toArray(new String[0]);
    }
    
    private String createJoinString(boolean fromLineItem) {
        StringBuilder builder = new StringBuilder();
        if (fromLineItem) {
            builder.append(" LEFT JOIN obj.paymentrequest.createdBy");
            builder.append(" LEFT JOIN obj.paymentrequest.paymenttype");
            builder.append(" LEFT JOIN obj.paymentrequest.payee");
        } else {
            builder.append(" LEFT JOIN obj.createdBy");
            builder.append(" LEFT JOIN obj.paymenttype");
            builder.append(" LEFT JOIN obj.payee");
        }
        return builder.toString();
    }

    private WhereParamTuple createWhereTuple(boolean fromLineItem, Paymentrequest pr) {
        WhereParamTuple result = new WhereParamTuple();
        String prefix = fromLineItem ? "obj.paymentrequest." : "obj.";
        List<Object> paramValues = new ArrayList<Object>();
        StringBuilder where = new StringBuilder();
        if (NumberUtil.INSTANCE.getIntValue(pr.getId()) > 0) {
            where.append(prefix + "id = ?");
            paramValues.add(pr.getId());

        }
    

        if (pr.getPayee() != null) {
//            if (NumberUtil.INSTANCE.getIntValue(pr.getPayee().getId()) > 0) {
//                if (where.length() > 0) {
//                    where.append(" AND ");
//                }
//                where.append(prefix + "payee.id = ?");
//                paramValues.add(pr.getPayee().getId());
//            }

            if (StringUtils.isNotBlank(pr.getPayee().getPayeeName())) {
                if (where.length() > 0) {
                    where.append(" AND ");
                }

                where.append("UPPER(" + prefix + "payee.payeeName) = ?");
                paramValues.add( pr.getPayee().getPayeeName().toUpperCase());
            }
        }

        if (pr.getCreatedBy() != null && NumberUtil.INSTANCE.getIntValue(pr.getCreatedBy().getId()) > 0) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append(prefix + "createdBy.id = ?");
            paramValues.add(pr.getCreatedBy().getId());
        }

        if (StringUtils.isNotBlank(pr.getStatusCode())) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append("UPPER(" + prefix + "statusCode) = ?");
            paramValues.add(pr.getStatusCode().toUpperCase());
        }

        if (pr.getStartAmount() != null || pr.getEndAmount() != null) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append(prefix + "amount IS NOT NULL");

            if (pr.getStartAmount() != null) {
                where.append(" AND " + prefix + "amount >= ?");
                paramValues.add(pr.getStartAmount());
            }

            if (pr.getEndAmount() != null) {
                where.append(" AND " + prefix + "amount <= ?");
                paramValues.add(pr.getEndAmount());
            }
        }

        if (pr.getStartDate() != null && pr.getEndDate() != null) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append(prefix + "creationDate BETWEEN ? AND ?");
            paramValues.add(DateUtil2.INSTANCE.getSodDate(pr.getStartDate()));
            paramValues.add(DateUtil2.INSTANCE.getEodDate(pr.getEndDate()));
        } else if (pr.getStartDate() != null) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append(prefix + "creationDate >= ?");
            paramValues.add(DateUtil2.INSTANCE.getSodDate(pr.getStartDate()));
        } else if (pr.getEndDate() != null) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append(prefix + "creationDate <= ?");
            paramValues.add(DateUtil2.INSTANCE.getSodDate(pr.getEndDate()));
        }

        if (pr.getPaymenttype() != null && NumberUtil.INSTANCE.getIntValue(pr.getPaymenttype().getId()) > 0) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append(prefix + "paymenttype.id = ?");
            paramValues.add(pr.getPaymenttype().getId());
        }

        if (StringUtils.isNotBlank(pr.getPaymentmethod())) {
            if (where.length() > 0) {
                where.append(" AND ");
            }
            where.append("UPPER(" + prefix + "paymentmethod) = ?");
            paramValues.add(pr.getPaymentmethod().toUpperCase());
        }
        
     

        result.paramValues = paramValues;
        result.whereClause = where.toString();
        return result;
    }

    private class WhereParamTuple {
        List<Object> paramValues = new ArrayList<Object>();
        String whereClause = "";
    }
    /**
     * 
      @return list of PaymentRequest whose statusCode isnot cancelled and rejected,dateforwardedforSignaure is null and datepaid is null 

     */
    public List<Paymentrequest> getPaymentListForNeverSelectedSignature(){
    	 String query="SELECT p FROM Paymentrequest p WHERE p.datepaid IS NULL and  p.statusCode!= 'Cancelled' and p.statusCode!='Rejected' and p.dateforwardedforsignature IS NULL";
  	   
    	 return  this.dao.getResultList(query);
    	
    }
    /**
     * 
     * @return list of PaymentRequest whose statusCode isnot cancelled,rejected and paid for UnpaidSignature
     */
    public List<Paymentrequest> getPaymentListForUnPaidSignature(){
   	 String query="SELECT p FROM Paymentrequest p WHERE p.datepaid IS NULL and  p.statusCode!= 'Cancelled' and p.statusCode!='Rejected' and p.statusCode!='Paid'";
 	   
   	 return  this.dao.getResultList(query);
   	
   }
    
    
}
